﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Data.Common;

namespace Vbyte.SharpOrm.SqlDb
{
    /// <summary>
    /// MS SqlServer实现
    /// </summary>
    public class SqlServer : SqlDbBase
    {
        /// <summary>
        /// 建立一个SqlServer实例
        /// </summary>
        public SqlServer()
        { 
        
        }
        
        /// <summary>
        /// 通过数据库连接字符串建立一个SqlServer实例
        /// </summary>
        /// <param name="connStr">数据库连接字符串</param>
        public SqlServer(string connStr)
        {
            ConnectionString = connStr;
        }

        /// <summary>
        /// 数据库名
        /// </summary>
        public string DbName { get; set; }

        private string sqldboName = "dbo";

        /// <summary>
        /// 用户帐户
        /// </summary>
        public string UserID { get; set; }

        /// <summary>
        /// 获取或设置要连接到的 SQL Server 实例的名称或网络地址。
        /// </summary>
        public string DataSource { get; set; }

        /// <summary>
        /// 用户密码
        /// </summary>
        public string Password { get; set; }

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public override string ConnectionString 
        {
            get 
            { 
                return base.ConnectionString; 
            }
            set 
            {
                base.ConnectionString = value;
                Initialize();
            }
        }

        private SqlConnection conn = new SqlConnection();

        private void Initialize()
        {
            SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(ConnectionString);
            DbName = cb.InitialCatalog;
            UserID = cb.UserID;

            if (string.Compare(UserID, "sa", true) != 0)
            {
                sqldboName = UserID;
            }

            Password = cb.Password;
            DataSource = cb.DataSource;

            CheckConnection();
        }

        private bool isSql2000 = false;

        /// <summary>
        /// 判断当前数据库是否是2000版本
        /// </summary>
        public bool IsSqlServer2000()
        { 
            return isSql2000;
        }

        private void CheckConnection()
        {
            conn = (CurrentConnection == null) ? new SqlConnection() : (SqlConnection)CurrentConnection;
            conn.ConnectionString = ConnectionString;
            if (conn.State != ConnectionState.Open) conn.Open();

            SqlCommand cmd = new SqlCommand("select @@Version", conn);
            isSql2000 = cmd.ExecuteScalar().ToString().Trim().StartsWith("Microsoft SQL Server 2000");
            cmd.Dispose();

        }

        /// <summary>
        /// 转义单引号
        /// </summary>
        internal static string EscapeSQM(string sql)
        {
            if (string.IsNullOrEmpty(sql)) return sql;
            return sql.Replace("'", "''");
        }

        /// <summary>
        /// 判定是否存在数据库
        /// </summary>
        /// <param name="dbname">数据库名或路径</param>
        /// <returns></returns>
        public override bool ExsitsDb(string dbname)
        {
            bool exists = false;
            SqlCommand cmd = new SqlCommand("sp_databases", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                if (string.Compare(dbname, reader["DATABASE_NAME"].ToString(), true) == 0)
                {
                    exists = true;
                    break;
                }
            }
            cmd.Dispose();
            return exists;
        }

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="dbname">数据库名或路径</param>
        public override void CreateDb(string dbname)
        {
            SqlCommand cmd = new SqlCommand("create database " + dbname, conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }

        /// <summary>
        /// 创建带密码的数据库
        /// </summary>
        /// <param name="dbname">数据库名或文件路径</param>
        /// <param name="dbpwd">数据库密码</param>
        public override void CreateDb(string dbname, string dbpwd)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="dbName">数据库名或路径</param>
        public override void DropDb(string dbName)
        {
            SqlCommand cmd = new SqlCommand("drop database " + dbName, conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }

        /// <summary>
        /// 判定是否存在指定表名
        /// </summary>
        /// <param name="tabName">数据库表名</param>
        /// <returns></returns>
        public override bool ExistsTable(string tabName)
        {
            bool exists = false;
            string sql = (isSql2000) ? "SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[" + tabName + "]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1"
                : "SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + tabName +"]') AND type in (N'U')";
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                exists = true;
            }
            reader.Close();
            cmd.Dispose();
            return exists;
        }

        /// <summary>
        /// 根据统一表定义创建表
        /// </summary>
        /// <param name="tabDefine">数据表定义</param>
        public override void CreateTable(SqlTable tabDefine)
        {
            //完善依赖关系
            if (ExistsTable(tabDefine.Name)) return;

            StringBuilder sql = new StringBuilder();
            StringBuilder sqlComment = new StringBuilder();
            sql.AppendFormat("CREATE TABLE {0}", tabDefine.Name);
            sql.AppendLine();
            sql.Append("(");
            sql.AppendLine();

            bool blnPrimaryKeyExits = false;

            #region 建表结构及字段描述
            for (int i = 0, j=tabDefine.Columns.Length; i < j; i++)
            {
                SqlTableColumn col = tabDefine.Columns[i];
                if (col.IsPrimary) blnPrimaryKeyExits = true;

                sql.AppendFormat(" {0} {1} {2}{3}{4}{5}",  col.Name,
                    col.DataType,
                    col.IsNullable ? "null" : "not null",
                    (col.IsIdentity && col.AutoIncrement != null) ? " identity" + col.AutoIncrement.ToString() : "",
                    col.Default != null ? " default " + col.Default.ToString() : "",
                    col.IsPrimary ? (tabDefine.PrimaryKeys.Length > 1 ? "" : " Primary key") : ""
                  );

                if (i < j - 1) { sql.Append(","); }
                sql.AppendLine();

                if (!string.IsNullOrEmpty(col.Description))
                {
                    sqlComment.AppendLine(); 
                    sqlComment.AppendFormat("EXECUTE sp_addextendedproperty N'MS_Description', N'{2}', N'SCHEMA', N'{0}', N'TABLE', N'{1}', N'COLUMN', N'{3}'",
                        sqldboName,
                        tabDefine.Name,
                        EscapeSQM(col.Description),
                        col.Name);
                    sqlComment.AppendLine();
                    sqlComment.AppendLine("GO");
                }
            }
            #endregion

            sql.AppendLine(")");
            sql.AppendLine("GO");

            #region 表描述
            if (!string.IsNullOrEmpty(tabDefine.Description))
            {
                sql.AppendLine(); 
                sql.AppendFormat("EXECUTE sp_addextendedproperty N'MS_Description', N'{2}', N'SCHEMA', N'{0}', N'TABLE', N'{1}', NULL, NULL",
                    sqldboName,
                    tabDefine.Name,
                    EscapeSQM(tabDefine.Description));
                sql.AppendLine();
                sql.AppendLine("GO");
            }
            #endregion

            #region 表索引
            bool blnClusteredCreate = false;
            string currentClusteredKey = "NONCLUSTERED";
            foreach (SqlIndexConfig idx in tabDefine.Indexes)
            {
                
                if (idx.IsClustered && 
                    !blnClusteredCreate && 
                    !blnPrimaryKeyExits)
                {
                    //创建唯一聚焦索引
                    currentClusteredKey = "CLUSTERED";
                    blnClusteredCreate = true;
                }
                else
                {
                    currentClusteredKey = "NONCLUSTERED";
                }

                sqlComment.AppendLine();
                sqlComment.AppendFormat("CREATE{0} {4} INDEX {1} ON {2}.{3}",
                    idx.IsUnique ? " UNIQUE" : "",
                    idx.IndexName,
                    sqldboName,
                    tabDefine.Name,
                    currentClusteredKey);
                sqlComment.AppendLine("(");

                for (int i = 0, j = idx.ColumnIndex.Length; i < j; i++)
                {
                    if (i == 0)
                    {
                        sqlComment.Append(idx.ColumnIndex[i].ToString());
                    }
                    else
                    {
                        sqlComment.Append("," + idx.ColumnIndex[i].ToString());
                    }
                    sqlComment.AppendLine();
                }
                sqlComment.AppendLine(")");
                //Primary(主)文件组
                sqlComment.AppendLine("WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]");
                sqlComment.AppendLine("GO");

            }
            #endregion

            #region 主键、标志键约束
            if (tabDefine.PrimaryKeys.Length > 1)
            {
                //主键聚焦索引
                SqlConstraint primaryConstraint = new SqlConstraint("PK_" + tabDefine.Name,
                    string.Concat(" PRIMARY KEY CLUSTERED", "(",
                     string.Join(",", tabDefine.PrimaryKeys),
	                 ") WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"));
                
                sqlComment.AppendLine();
                sqlComment.AppendFormat("ALTER TABLE {0}.{1} ADD CONSTRAINT ", sqldboName, tabDefine.Name);
                sqlComment.AppendLine();
                sqlComment.AppendLine(primaryConstraint.ToString());
                sqlComment.AppendLine("GO");
            }

            if (tabDefine.IdentityKeys.Length > 1)
            {
                //唯一的非聚焦(聚焦、如果可能)索引
                currentClusteredKey = "NONCLUSTERED";
                if (blnClusteredCreate == false) currentClusteredKey = "CLUSTERED";

                SqlConstraint primaryConstraint = new SqlConstraint("IDX_" + tabDefine.Name,
                    string.Concat(" UNIQUE ", currentClusteredKey, " (",
                     string.Join(",", tabDefine.IdentityKeys),
                     ") WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"));
                
                sqlComment.AppendLine();
                sqlComment.AppendFormat("ALTER TABLE {0}.{1} ADD CONSTRAINT ", sqldboName, tabDefine.Name);
                sqlComment.AppendLine();
                sqlComment.AppendLine(primaryConstraint.ToString());
                sqlComment.AppendLine("GO");
            }
            #endregion

            #region 约束(主要为外键)
            foreach (SqlConstraint ct in tabDefine.Constains)
            {
                sqlComment.AppendLine(); 
                sqlComment.AppendFormat("ALTER TABLE {0}.{1} ADD CONSTRAINT ", sqldboName, tabDefine.Name);
                sqlComment.AppendLine();
                sqlComment.AppendLine(ct.ToString()
                    .Replace("[dbo]", sqldboName)
                    .Replace("[table]", tabDefine.Name));
                sqlComment.AppendLine("GO");
            }
            #endregion

            //Console.WriteLine(sql.ToString() + sqlComment.ToString());

            #region 执行建表语句
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            string lineStr;
            StringReader reader = new StringReader(sql.ToString() + sqlComment.ToString());
            sql.Remove(0, sql.Length);
            while ((lineStr = reader.ReadLine()) != null)
            {
                if (lineStr == "GO")
                {
                    cmd.CommandText = sql.ToString();
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception) {  }
                    sql.Remove(0, sql.Length);
                }
                else
                {
                    sql.AppendLine(lineStr);
                }
            }
            cmd.Dispose();
            #endregion

        }

        /// <summary>
        /// 删除数据库中的表
        /// </summary>
        /// <param name="tabName">数据库表名</param>
        public override void DropTable(string tabName)
        {
            SqlCommand cmd = new SqlCommand("drop table " + tabName, conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }

        /// <summary>
        /// 对表的修改，执行原生的SQL语句
        /// </summary>
        /// <param name="NativeSql">原生的SQL语句</param>
        public override void AlterTable(string NativeSql)
        {
            SqlCommand cmd = new SqlCommand(NativeSql, conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }

        /// <summary>
        /// 判定是否存在指定的列名
        /// </summary>
        /// <param name="tabName">数据库表名</param>
        /// <param name="colName">数据表中的列名</param>
        /// <returns></returns>
        public override bool ExistsColumn(string tabName, string colName)
        {
            bool exists = true; 
            string sql = string.Format("Select Top 1 {0} from {1}", tabName, colName);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                object result = cmd.ExecuteScalar();
                cmd.Dispose();
            }
            catch (Exception)
            {
                exists = false;
            }
            return exists;
        }

        /// <summary>
        /// 在指定表名中添加新的列
        /// </summary>
        /// <param name="tabName">数据库表名</param>
        /// <param name="column">数据列定义</param>
        public override void AddColumn(string tabName, SqlTableColumn column)
        {
             /*
            //alter table aaa drop column b
            //alter table aaa add  b text null
            //alter table aaa add  b datetime not null default getdate()
            // alter table gw_Publish_PageTemplet drop constraint pageTempletOnlyOne
              删除约束  
              alter   table   dept   drop   unique   (dname,loc)       --指定约束的定义内容  
              alter   table   dept   drop   constraint   dept_dname_loc_uk(约束名）
            */
            string colSQL = string.Format(" {0} {1} {2}{3}{4}{5}", column.Name,
                    column.DataType,
                    column.IsNullable ? "null" : "not null",
                    (column.IsIdentity && column.AutoIncrement != null) ? " identity" + column.AutoIncrement.ToString() : "",
                    column.Default != null ? " default " + column.Default.ToString() + " with values " : "",
                    column.IsPrimary ? " Primary key" : ""
                  );

            string sql = string.Format("alter table {0} add \r\n{1}",
                tabName, colSQL);

            AlterTable(sql);
        }

        /// <summary>
        /// 删除指定表名中的数据列
        /// </summary>
        /// <param name="tabName">数据库表名</param>
        /// <param name="colName">数据表中的列名</param>
        public override void DropColumn(string tabName, string colName)
        {
            //删除约束和相关索引
            /*
             -- this script drops the default constraint which is generated by the setting of default value.
            DECLARE @tablename VARCHAR(100), @columnname VARCHAR(100), @tab VARCHAR(100)
            SET @tablename='CountryGroupEmailAndWaitAux'
            SET @columnname='actionOfHasNoValidEmail'

            declare @defname varchar(100)
            declare @cmd varchar(100)

            select @defname = name
            FROM sysobjects so
            JOIN sysconstraints sc
            ON so.id = sc.constid
            WHERE object_name(so.parent_obj) = @tablename
            AND so.xtype = 'D'
            AND sc.colid =
            (SELECT colid FROM syscolumns
            WHERE id = object_id(@tablename) AND
            name = @columnname)

            select @cmd='alter table '+ @tablename+ ' drop constraint '+ @defname
            if @cmd is null print 'No default constraint to drop'
            exec (@cmd)

            在删除对应的默认约束后，执行：
            ALTER TABLE table1 DROP COLUMN column1
            即可删除字段。
 

             MS SQL Server的语法：
             DROP INDEX table_name.index_name
             http://www.cnblogs.com/replace/category/54116.html
             */
            string sql = string.Format("Alter Table {0} Drop column {1}", tabName, colName);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }

        /// <summary>
        /// 获取或设置该实例上的数据库连接
        /// </summary>
        /// <value>数据库连接对象</value>
        public override DbConnection CurrentConnection
        {
            get
            {
                return base.CurrentConnection;
            }

            set
            {
                if (base.CurrentConnection != null)
                {
                    if (base.CurrentConnection.State == ConnectionState.Open) base.CurrentConnection.Close();
                    base.CurrentConnection.Dispose();
                }

                base.CurrentConnection = (SqlConnection)value;
            }
        }

        /// <summary>
        /// 执行与释放或重置非托管资源相关的应用程序定义的任务。
        /// </summary>
        public override void Dispose()
        {
            base.Dispose();

            if (conn != null)
            {
                if (conn.State != ConnectionState.Closed) conn.Close();
                conn.Dispose();
            }
        }

    }
}
